Во второй части проекта мне нужно написать несколько SQL-запросов в Jupyter Notebook.

Необходимые данные находятся в таблицах схемы stackoverflow. Я подключусь к базе с помощью SQLAlchemy.

Некоторые задания включают дополнительные вопросы. На часть вопросов я отвечу текстом, а для некоторых создам визуализации.

Конфигурация для подключения к базе данных data-analyst-advanced-sql

Эта база данных содержит схему stackoverflow, с которой мы будем работать в проекте

Создание подключения

Пример запроса к базе данных

sample_df является pandas-датафреймом.

Задание 1

Выведите общую сумму просмотров постов за каждый месяц 2008 года. Если данных за какой-либо месяц в базе нет, такой месяц можно пропустить. Результат отсортируйте по убыванию общего количества просмотров.

month_date total_views
2008-09-01 452928568
2008-10-01 365400138
... ...

Проанализируйте итоговую таблицу. Отличаются ли данные за разные месяцы? С чем могут быть связаны отличия?

Данные за разные месяцы отличаются. Мы видим, что в июле количество просмотров было самым низким. После июля следующий месяц по числу минимальных просмотров — это август. Наибольшее количество просмотров было в сентябре. Так как StackOverflow — это сервис вопросов и ответов о программировании, можно предположить, что у посетителей возросла образовательная активность с наступлением осени. Прослеживается сезонность.

Задание 2

Выведите имена самых активных пользователей, которые в первый месяц после регистрации (включая день регистрации) дали больше 100 ответов. Вопросы, которые задавали пользователи, не учитывайте. Для каждого имени пользователя выведите количество уникальных значений user_id. Отсортируйте результат по полю с именами в лексикографическом порядке.

display_name count
1800 INFORMATION 1
Adam Bellaire 1
Adam Davis 1
... ...

Какие аномалии наблюдаются в данных? О чём они говорят?

Количество имён самых активных пользователей, которые в первый месяц после регистрации, включая день регистрации, дали больше 100 ответов, совпадает с количеством их уникальных пользовательских идентификаторов. Это говорит о том, что каждый из таких активных пользователей использовал только один аккаунт от одного user_id.

Задание 3

Выведите количество постов за 2008 год по месяцам. Отберите посты от пользователей, которые зарегистрировались в сентябре 2008 года и сделали хотя бы один пост в декабре того же года. Отсортируйте таблицу по значению месяца по убыванию.

month count
2008-12-01 17641
2008-11-01 18294
... ...

Изучите данные: есть ли в них аномалии? Предположите, почему могли появиться аномальные значения.

Получается несколько нелогично, что у пользователей, которые зарегистрировались в сентябре есть посты, опубликованные в августе. Возможно, дело в том, что на сервере указано время по Гринвичу UTC-0 и в момент, когда по Московскому времени UTC+3 наступил сентябрь, у этих пользователей было 3 часа на создание постов, которые «ушли в август» по времени сервера. Изучим, сколько постов создали в августе те пользователи, которые зарегистрировались в сентябре.

Предположение о разных часовых поясах отбрасываем. В таблице report содержатся те 32 записи, которые были посчитаны за август. Таблица отсортирована сначала по полю с датой регистрации пользователя, затем по полю с датой создания поста. В ней всего два уникальных user_id — 31505 и 4381. Вероятно, у этих пользователей в результате какого-либо сбоя реальная дата регистрации оказалась перезаписана.

Задание 4

Используя данные о постах, выведите несколько полей:

Данные в таблице должны быть отсортированы по возрастанию идентификаторов пользователей, а данные об одном и том же пользователе — по возрастанию даты создания поста.

user_id creation_date views_count cumulative_count
1 2008-07-31 23:41:00 480476 480476
1 2008-07-31 23:55:38 136033 616509
1 2008-07-31 23:56:41 0 616509
... ... ... ...
2 2008-07-31 23:56:41 79087 79087
2 2008-08-01 05:09:56 65443 144530
... ... ... ...

Задание 5

Найдите среднее количество постов пользователей в день за август 2008 года. Отберите данные о пользователях, которые опубликовали больше 120 постов за август. Дни без публикаций не учитывайте.

Отсортируйте результат по возрастанию среднего количества постов. Значения можно не округлять.

user_id avg_daily
116 4.777778
234 5.208333
... ...

Задание 6

Сколько в среднем дней в период с 1 по 7 декабря 2008 года пользователи взаимодействовали с платформой? Для каждого пользователя отберите дни, в которые он или она опубликовали хотя бы один пост. Нужно получить одно целое число — не забудьте округлить результат.

result
<целое число>

Проанализируйте итоговую таблицу — какие выводы можно сделать?

Среднее значение очень неустойчиво к выбросам в данных. Число 2 может означать то, что с 1 по 7 декабря многие пользователи практически не совершали взаимодействие с платформой. Построим гистограмму для того, чтобы оценить распределение числа дней, которые провели пользователи на платформе в рассматриваемый период.

Более, чем 2000 пользователей платформы в период с 1 по 7 декабря 2008 года совершали с ней взаимодействие в течение одного дня. Мы видим, что распределение убывает по экспоненте — большему числу дней взаимодействия соответствует меньшее число пользователей. Это объясняет, почему среднее значение равно двум дням.

Задание 7

Выведите историю активности каждого пользователя в таком виде: идентификатор пользователя, дата публикации поста. Отсортируйте вывод по возрастанию идентификаторов пользователей, а для каждого пользователя — по возрастанию даты публикации.

Добавьте в таблицу новое поле: для каждого поста в нём будет указано название месяца предпоследней публикации пользователя относительно текущей. Если такой публикации нет, укажите NULL. Python автоматически поменяет NULL на None, но дополнительно преобразовывать значения None вам не нужно.

Посмотрите внимательно на образец таблицы: для первых двух постов предпоследней публикации нет, но, начиная с третьего поста, в новое поле входит нужный месяц. Для следующего пользователя в первые две записи поля second_last_month тоже войдёт NULL.

user_id creation_date second_last_month
1 2008-07-31 23:41:00 None
1 2008-07-31 23:55:38 None
1 2008-07-31 23:56:41 July
1 2008-08-04 02:45:08 July
1 2008-08-04 04:31:03 July
1 2008-08-04 08:04:42 August
... ... ...

Задание 8

Рассчитайте аналог Retention Rate по месяцам для пользователей StackOverflow. Объедините пользователей в когорты по месяцу их первого поста. Возвращение определяйте по наличию поста в текущем месяце.

cohort_dt session_date users_cnt cohort_users_cnt retention_rate
2008-07-01 00:00:00 2008-07-01 00:00:00 3 3 100
2008-07-01 00:00:00 2008-08-01 00:00:00 2 3 66,67
2008-07-01 00:00:00 2008-09-01 00:00:00 1 3 33,33
2008-07-01 00:00:00 2008-10-01 00:00:00 2 3 66,67
2008-07-01 00:00:00 2008-11-01 00:00:00 1 3 33,33
2008-07-01 00:00:00 2008-12-01 00:00:00 2 3 66,67
2008-08-01 00:00:00 2008-08-01 00:00:00 2151 2151 100
... ... ... ... ...
Подсказка Вспомните, как выглядел запрос для расчёта Retention Rate в теории. Создайте две временные таблицы: `profile` и `sessions` (в ней будет информация о публикациях), а затем используйте их в основном запросе. Во временной таблице `profile` вам понадобятся три поля: - идентификатор пользователя; - дата первого поста пользователя, усечённая до месяца (признак начала когорты); - количество пользователей этой когорты.

Постройте тепловую карту Retention Rate. Какие аномалии или другие необычные явления удалось выявить? Сформулируйте гипотезы о возможных причинах.

На тепловой карте видно, что декабрьская когорта не успела прожить достаточное количество месяцев, поэтому сказать о ней мы ничего не можем, для ноябрьской у нас есть данные только для первого лайфтайма. Можно выделить хорошие показатели удержания для когорты августа — практически по всем лайфтаймам она показала лучшие результаты. Это напрямую говорит и о качестве привлечённых пользователей и о том, что первый лайфтайм этой когорты попал на сентябрь. Ранее мы уже выдвигали гипотезу о том, что с наступлением осени образовательная активность повышается. Когорта июля малочисленна: в неё входит всего 3 пользователя и на графике видно, что на первый лайфтайм из этой когорты вернулись 2 пользователя, на второй — 1, на третий снова 2, на четвёртый снова 1 и на пятый — опять 2. Показатели удержания выглядят несколько неестественно как раз по той причине, что в когорту входят всего 3 пользователя.

Задание 9

На сколько процентов менялось количество постов ежемесячно с 1 сентября по 31 декабря 2008 года? Отобразите таблицу со следующими полями:

Если постов стало меньше, значение процента должно быть отрицательным, если больше — положительным. Округлите значение процента до двух знаков после запятой.

Напомним, что при делении одного целого числа на другое в PostgreSQL в результате получится целое число, округлённое до ближайшего целого вниз. Чтобы этого избежать, переведите делимое в тип numeric.

creation_month posts_count percentage
9 70731 Nan
10 63102 -10.33
... ... ...

Постройте круговую диаграмму с количеством постов по месяцам.

Задание 10

Выгрузите данные активности пользователя, который опубликовал больше всего постов за всё время. Выведите данные за октябрь 2008 года в таком виде:

week_creation creation_date
40 2008-10-05 09:00:58
41 2008-10-12 21:22:23
... ...